﻿#region Copyrights Notice
//©2009 Microsoft Corporation.  All rights reserved.
#endregion

using System;
using System.Data;
using System.Data.Common;
using System.Globalization;
using Microsoft.CISG.BitlockerRecovery.CDE;
using Microsoft.Practices.EnterpriseLibrary.Data;
[assembly: CLSCompliant(true)]

namespace Microsoft.CISG.BitlockerRecovery.DL
{
    public static class CommonDL
    {
        public static IdentityCDE SearchGUID(string guid,string requestor,string requestedfor, int roleId)
        {
            IdentityCDE UserIdentity = new IdentityCDE();

            Database db = DatabaseFactory.CreateDatabase("BitDBSQL");
            string sqlCommand = "usp_GetBitlockerRecoveryInfoByIDAlias";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(dbCommand, "@RecoverypasswordID", DbType.String, guid);
            db.AddInParameter(dbCommand, "@RetrieverAlias", DbType.String, requestor);
            db.AddInParameter(dbCommand, "@RequestedFor", DbType.String, requestedfor);
            db.AddInParameter(dbCommand, "@RetrieverRoleID", DbType.Int32, roleId);

            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                while (dr.Read())
                {

                    UserIdentity.MachineName = dr["MachineName"].ToString();
                    UserIdentity.RecoveryGUID = dr["RecoveryGUID"].ToString();
                    UserIdentity.RecoveryPassword = (byte[])dr["RecoveryPassword"];
                    UserIdentity.KeyPackage = (byte[])dr["KeyPackage"];
                    if (!dr["TPMHash"].Equals(DBNull.Value))
                    {
                        UserIdentity.TPMHash = (byte[])dr["TPMHash"];
                        UserIdentity.TPMFound = true;
                    }
                    else
                        UserIdentity.TPMFound = false;
                    UserIdentity.MachineOwner = dr["Owner"].ToString();
                }

            }

            return UserIdentity;
        }
        public static DataSet Search(string searchValue, string requestor, string requestedfor, int roleId)
        {

            Database db = DatabaseFactory.CreateDatabase("BitDBSQL");
            string sqlCommand = "usp_GetBitlockerRecoveryInfoAdmin";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(dbCommand, "@SearchParameter", DbType.String, searchValue);
            db.AddInParameter(dbCommand, "@RetrieverAlias", DbType.String, requestor);
            db.AddInParameter(dbCommand, "@RequestedFor", DbType.String, requestedfor);
            db.AddInParameter(dbCommand, "@RetrieverRoleID", DbType.Int32, roleId);

            DataTable srcResults = new DataTable("SearchResults");
            srcResults.Locale = CultureInfo.InvariantCulture;
            srcResults.Columns.Add("ID", typeof(Int32));
            srcResults.Columns.Add("MachineName", typeof(string));
            srcResults.Columns.Add("Owner", typeof(string));
            srcResults.Columns.Add("RecoveryGUID", typeof(string));
            srcResults.Columns.Add("RecoveryPassword", typeof(byte[]));
            srcResults.Columns.Add("KeyPackage", typeof(byte[]));
            srcResults.Columns.Add("TPMHash", typeof(byte[]));
            srcResults.Columns.Add("VolumeGUID", typeof(string));
            srcResults.Columns.Add("TimeStamp", typeof(string));
            srcResults.Columns.Add("RecoveryPasswordString", typeof(string));

            srcResults.Load(db.ExecuteReader(dbCommand));
            DataSet ds = new DataSet();
            ds.Locale = CultureInfo.InvariantCulture;
            ds.Tables.Add(srcResults);

            return ds;
        }
        public static string GetAppRoleGroup(int roleId)
        {
            string ReturnedRole=null;
            Database db = DatabaseFactory.CreateDatabase("BitDBSQL");
            string sqlCommand = "[usp_GetSecurityGroupByID]";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(dbCommand, "@RoleID", DbType.Int32, roleId);

            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                while (dr.Read())
                {
                    ReturnedRole = dr["SGName"].ToString();
                }
            }
            return ReturnedRole;
        }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1024:UsePropertiesWhereAppropriate")]
        public static DataSet GetFaqQuestions()
        {
            Database db = DatabaseFactory.CreateDatabase("BitDBSQL");
            string sqlCommand = "usp_GetFaq";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            DataSet ds = (DataSet)db.ExecuteDataSet(dbCommand);

            return ds;
        }
        public static void DeleteFaq(int faqId)
        {
            Database db = DatabaseFactory.CreateDatabase("BitDBSQL");
            string sqlCommand = "usp_DeleteFaq";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(dbCommand, "@FaqID", DbType.Int32, faqId);
            db.ExecuteNonQuery(dbCommand);
        }
        public static void UpdateFaq(int faqId, int sortOrder, string faqText)
        {
            Database db = DatabaseFactory.CreateDatabase("BitDBSQL");
            string sqlCommand = "usp_UpdateFaq";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(dbCommand, "@FaqID", DbType.Int32, faqId);
            db.AddInParameter(dbCommand, "@SortOrder", DbType.Int32, sortOrder);
            db.AddInParameter(dbCommand, "@FaqText", DbType.String, faqText);
            db.ExecuteNonQuery(dbCommand);

        }
        public static void InsertFaq(int sortOrder, string faqText)
        {
            Database db = DatabaseFactory.CreateDatabase("BitDBSQL");
            string sqlCommand = "usp_InsertFaq";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(dbCommand, "@SortOrder", DbType.Int32, sortOrder);
            db.AddInParameter(dbCommand, "@FaqText", DbType.String, faqText);
            db.ExecuteNonQuery(dbCommand);
        }
        public static string GetConfigValue(string configName)
        {
            string ArchivalDays = null;
            Database db = DatabaseFactory.CreateDatabase("BitDBSQL");
            string sqlCommand = "usp_GetConfigData";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(dbCommand, "@ConfigName", DbType.String, configName);

            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                while (dr.Read())
                {
                    ArchivalDays = dr["Value"].ToString();
                }
            }
            return ArchivalDays;
        }
        public static void UpdateConfigValue(string configName, string configValue)
        {
            Database db = DatabaseFactory.CreateDatabase("BitDBSQL");
            string sqlCommand = "usp_UpdateConfigData";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(dbCommand, "@ConfigName", DbType.String, configName);
            db.AddInParameter(dbCommand, "@ConfigValue", DbType.String, configValue);
            db.ExecuteNonQuery(dbCommand);
        }
        public static DataSet GetSummaryReport(DateTime startDate, DateTime endDate)
        {
            DateTime StartDate = Convert.ToDateTime(startDate);
            DateTime EndDate = Convert.ToDateTime(endDate);

            Database db = DatabaseFactory.CreateDatabase("BitDBSQL");
            string sqlCommand = "usp_GetActivitySummaryReport";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(dbCommand, "@StartDate", DbType.DateTime, StartDate);
            db.AddInParameter(dbCommand, "@EndDate", DbType.DateTime, EndDate);
            DataSet ds = (DataSet)db.ExecuteDataSet(dbCommand);

            return ds;

        }
        public static DataSet GetDetailReport(DateTime startDate, DateTime endDate)
        {
            DateTime StartDate = Convert.ToDateTime(startDate);
            DateTime EndDate = Convert.ToDateTime(endDate);

            Database db = DatabaseFactory.CreateDatabase("BitDBSQL");
            string sqlCommand = "usp_GetActivityDetailsReport";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(dbCommand, "@StartDate", DbType.DateTime, StartDate);
            db.AddInParameter(dbCommand, "@EndDate", DbType.DateTime, EndDate);
            DataSet ds = (DataSet)db.ExecuteDataSet(dbCommand);

            return ds;

        }
        public static EmailFormatCDE GetEmailFormat(string type)
        {
            EmailFormatCDE emailFormat = new EmailFormatCDE();

            Database db = DatabaseFactory.CreateDatabase("BitDBSQL");
            string sqlCommand = "usp_GetEmailInformation";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(dbCommand, "@NotificationType", DbType.String, type);

            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                while (dr.Read())
                {
                    emailFormat.FromAlias = dr["FromAlias"].ToString();
                    emailFormat.Purpose = dr["Purpose"].ToString();
                    emailFormat.Message = dr["Message"].ToString();
                }
            }
            return emailFormat;
        }
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1024:UsePropertiesWhereAppropriate")]
        public static DataSet GetEmailTemplateList()
        {
            Database db = DatabaseFactory.CreateDatabase("BitDBSQL");
            string sqlCommand = "usp_GetEmailTemplateList";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            DataSet ds = (DataSet)db.ExecuteDataSet(dbCommand);

            return ds;
        }
        public static void UpdateEmailTemplate(int notificationId, string from, string subject, string message)
        {
            Database db = DatabaseFactory.CreateDatabase("BitDBSQL");
            string sqlCommand = "usp_UpdateEmailTemplate";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(dbCommand, "@NotifID", DbType.Int32, notificationId);
            db.AddInParameter(dbCommand, "@From", DbType.String, from);
            db.AddInParameter(dbCommand, "@Subject", DbType.String, subject);
            db.AddInParameter(dbCommand, "@Message", DbType.String, message);
            db.ExecuteNonQuery(dbCommand);
        }
    }
}
